Imports System.Data.SqlClient
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Crystal.DatabaseManagement

Namespace BusinessLayer
    Public Class cImportFunds

#Region "Declare"
        Public Event EntInvRecordAdded()
        Private _DbFieldID As Integer
        Private _FormatID As Integer
        Private _Inserted As Integer
        Private _Updated As Integer
        Private _SourceTypeCode As String
        Private _ESourceID As Integer

#End Region

#Region "Properties"
        Public Property ESourceID() As Integer
            Get
                Return _ESourceID
            End Get
            Set(ByVal value As Integer)
                _ESourceID = value
            End Set
        End Property

        Public Property SourceTypeCode() As String
            Get
                Return _SourceTypeCode
            End Get
            Set(ByVal value As String)
                _SourceTypeCode = value
            End Set
        End Property

        Public Property Inserted() As Integer
            Get
                Return _Inserted
            End Get
            Set(ByVal value As Integer)
                _Inserted = value
            End Set
        End Property

        Public Property Updated() As Integer
            Get
                Return _Updated
            End Get
            Set(ByVal value As Integer)
                _Updated = value
            End Set
        End Property

        Public Property DbFieldID() As Integer
            Get
                Return _DbFieldID
            End Get
            Set(ByVal value As Integer)
                _DbFieldID = value
            End Set
        End Property

        Public Property TemplateID() As Integer
            Get
                Return _FormatID
            End Get
            Set(ByVal value As Integer)
                _FormatID = value
            End Set
        End Property

        Private _TotalNameChange As Integer
        Public Property TotalNameChange() As Integer
            Get
                Return _TotalNameChange
            End Get
            Set(ByVal value As Integer)
                _TotalNameChange = value
            End Set
        End Property


        Private _Totalacquired As Integer
        Public Property Totalacquired() As Integer
            Get
                Return _Totalacquired
            End Get
            Set(ByVal value As Integer)
                _Totalacquired = value
            End Set
        End Property


        Private _TotalMerged As Integer
        Public Property TotalMerged() As Integer
            Get
                Return _TotalMerged
            End Get
            Set(ByVal value As Integer)
                _TotalMerged = value
            End Set
        End Property

#End Region

#Region "Methods"

        ''' <summary>
        ''' Calls the procedure to Import Records
        ''' </summary>
        ''' <param name="dtInandOutTable"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function ImportFunds(ByRef dtInandOutTable As DataTable) As Boolean
            'Try
            dtInandOutTable = CreateDataTableStructure(dtInandOutTable)

            Return True
            'Catch ex As Exception
            '    Throw ex
            'End Try

        End Function

        ''' <summary>
        ''' Genrate table To insert in databse (table are generated in same sequence as in datbase)
        ''' </summary>
        ''' <param name="dtExcelClone">Genrate datatable for importing</param>
        ''' <remarks></remarks>

        Private Function CreateDataTableStructure(ByVal dtExcelClone As DataTable) As DataTable
            Dim Fund As New DataTable
            ' Dim DC As New DataColumn
            Try
                Fund.Columns.Add("EntityID", Type.GetType("System.String"))
                Fund.Columns.Add("FundId", Type.GetType("System.String"))
                Fund.Columns.Add("FundName", Type.GetType("System.String"))
                Fund.Columns.Add("TypeID", Type.GetType("System.Int32"))
                Fund.Columns.Add("StyleID", Type.GetType("System.Int32"))
                Fund.Columns.Add("FundDomicileID", Type.GetType("System.Int32"))
                Fund.Columns.Add("TurnoverLabelID", Type.GetType("System.String"))
                Fund.Columns.Add("CurrentReportdate", Type.GetType("System.String"))
                Fund.Columns.Add("PortfolioValue", Type.GetType("System.String"))
                Fund.Columns.Add("PERatio", Type.GetType("System.Double"))
                Fund.Columns.Add("PBRatio", Type.GetType("System.Double"))
                Fund.Columns.Add("DividentYield", Type.GetType("System.Double"))
                Fund.Columns.Add("SalesGrowth", Type.GetType("System.Double"))
                Fund.Columns.Add("PriceMomentum", Type.GetType("System.Double"))
                Fund.Columns.Add("RelativeStrength", Type.GetType("System.Double"))
                Fund.Columns.Add("Beta", Type.GetType("System.Double"))
                Fund.Columns.Add("IsFundSame", Type.GetType("System.Boolean"))

                Fund.Merge(dtExcelClone, True, MissingSchemaAction.Ignore)
                Return BulkInsertFund(GetXMlFromDataTable(Fund))

            Catch ex As Exception
                Throw ex

            End Try

        End Function

        ''' <summary>
        ''' Genrates Xml from datatable
        ''' </summary>
        ''' <param name="dtExcelClone"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Protected Function GetXMlFromDataTable(ByVal dtExcelClone As DataTable) As String
            Dim InvestorDetail As New System.Text.StringBuilder
            Dim Value As String = ""
            Dim RowCount As Integer = 1
            Dim GridRowsCount = dtExcelClone.Rows.Count
            Try
                InvestorDetail.Append("<ROOT>")
                For Each lrowInvestor As DataRow In dtExcelClone.Rows
                    If lrowInvestor("Fundname").ToString() = "" Then
                        Continue For
                    End If
                    InvestorDetail.Append("<Row ")
                    For Each lcolInvestor As DataColumn In dtExcelClone.Columns
                        Select Case lcolInvestor.ColumnName.ToLower
                            Case "portfoliovalue"
                                If Trim(lrowInvestor.Item("PortfolioValue").ToString) = "" Then Continue For
                            Case "peratio"
                                If Trim(lrowInvestor.Item("PERatio").ToString) = "" Then Continue For
                            Case "pbratio"
                                If Trim(lrowInvestor.Item("PBRatio").ToString) = "" Then Continue For
                            Case "dividentyield"
                                If Trim(lrowInvestor.Item("DividentYield").ToString) = "" Then Continue For
                            Case "salesgrowth"
                                If Trim(lrowInvestor.Item("SalesGrowth").ToString) = "" Then Continue For
                            Case "pricemomentum"
                                If Trim(lrowInvestor.Item("PriceMomentum").ToString) = "" Then Continue For
                            Case "relativestrength"
                                If Trim(lrowInvestor.Item("RelativeStrength").ToString) = "" Then Continue For
                            Case "beta"
                                If Trim(lrowInvestor.Item("Beta").ToString) = "" Then Continue For
                        End Select
                        InvestorDetail.Append(ReplaceSpecialChar(lcolInvestor.ColumnName))
                        InvestorDetail.Append("=""")
                        If (lrowInvestor.Item(lcolInvestor.ColumnName).ToString() = "") Then
                            Value = " "
                        Else
                            Value = Trim(lrowInvestor.Item(lcolInvestor.ColumnName).ToString())
                        End If
                        InvestorDetail.Append(ReplaceSpecialChar(Value))
                        InvestorDetail.Append(""" ")
                    Next
                    InvestorDetail.Append("/>")
                    InvestorDetail.Append(vbNewLine)
                    'Added by Pankaj Garg on 7 march 2009
                    If GridRowsCount < RowCount + (GridRowsCount / 100 * 3) Then
                    Else
                        If RowCount Mod 3 = 0 Then
                            RaiseEvent EntInvRecordAdded()
                        End If
                    End If
                    '-----------------------------------------------------------------

                    RowCount = RowCount + 1
                Next
                InvestorDetail.Append("</ROOT>")
                If InvestorDetail.ToString() Is Nothing Then
                    Dim lint As Integer = 0
                End If
                Return InvestorDetail.ToString()
            Catch ex As Exception
                Throw ex
            Finally
                InvestorDetail = Nothing
                dtExcelClone = Nothing
            End Try

        End Function


        ''' <summary>
        ''' replace the special character
        ''' </summary>
        ''' <param name="val"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Private Function ReplaceSpecialChar(ByVal val As String) As String
            Return val.Trim.Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;").Replace(">", "&gt;").Replace(ControlChars.Quote, "&quot;")
        End Function

        ''' <summary>
        ''' Adds Columnd to datatable
        ''' </summary>
        ''' <param name="dtExcelDuplicateCopy"></param>
        ''' <remarks></remarks>

        Public Sub AddColumnsToDatatable(ByRef dtExcelDuplicateCopy As DataTable)
            dtExcelDuplicateCopy.Columns.Add("Message", Type.GetType("System.Int32"))
            'dtExcelDuplicateCopy.Columns.Add("FundTypeID", Type.GetType("System.Int32"))
            'dtExcelDuplicateCopy.Columns.Add("StyleID", Type.GetType("System.Int32"))
            'dtExcelDuplicateCopy.Columns.Add("TurnoverID", Type.GetType("System.Int32"))
            'dtExcelDuplicateCopy.Columns.Add("CountryID", Type.GetType("System.Int32"))
            'dtExcelDuplicateCopy.Columns.Add("InvEntityID", Type.GetType("System.String"))
            'dtExcelDuplicateCopy.Columns.Add("FundID", Type.GetType("System.Int32"))
            'dtExcelDuplicateCopy.Columns.Add("EStatusID", Type.GetType("System.Int32"))
            dtExcelDuplicateCopy.Columns.Add("InvestorName", Type.GetType("System.String"))
            'dtExcelDuplicateCopy.Columns.Add("RowNumber", Type.GetType("System.Int32"))
        End Sub

        ''' <summary>
        ''' Checks All row is Blank
        ''' </summary>
        ''' <param name="lrowHistory"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function blnRowBlank(ByVal lrowHistory As DataRow)
            Dim Blank As Boolean = False
            For iCnt As Integer = 0 To lrowHistory.Table.Columns.Count - 1
                If lrowHistory.Table.Columns(iCnt).ColumnName = "IsFundSame" Then
                    Continue For
                End If
                If lrowHistory.Table.Columns(iCnt).ColumnName = "RowID" Then
                    Continue For
                End If
                If lrowHistory(iCnt).ToString <> "" Then

                    Blank = False
                    Exit For
                Else

                    Blank = True
                End If
            Next
            Return Blank
        End Function

        ''' <summary>
        ''' Checks the mapping Is valid
        ''' </summary>
        ''' <param name="formatID"></param>
        ''' <param name="dtFunds"></param>
        ''' <param name="dtExcelClone"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function MapWithDatabaseColumns(ByVal formatID As Integer, ByRef dtFunds As DataTable, ByRef dtExcelClone As DataTable) As Boolean

            Dim ExcelColName As String
            Dim ReturnVal As ExecutionResult
            Dim MappedCol As DataTable
            Dim ExcelClone As DataRow
            Dim ColumnName As New ArrayList
            Dim idr As IDataReader = Nothing
            Dim Excel As DataTable
            Try
                dtExcelClone = New DataTable
                dtExcelClone = dtFunds.Clone()
                dtExcelClone.Columns.Add("FileName", GetType(String))
                ExcelClone = dtExcelClone.NewRow
                Excel = dtFunds.Copy()

                ReturnVal = New ExecutionResult
                MappedCol = New DataTable
                For Each Col As DataColumn In Excel.Columns
                    ExcelColName = Col.ColumnName
                    MappedCol.Rows.Clear()
                    idr = MapDatabaseColumn(formatID, ExcelColName)
                    MappedCol.Load(idr)
                    ChangeDataReaderDataTable(MappedCol)
                    'ReturnVal = MapDatabaseColumn(formatID, ExcelColName)
                    If MappedCol.Rows(0).Item("FieldName") <> "UNMAPPED" Then
                        'MappedCol = CType(ReturnVal.ResultObject, DataSet).Tables(0)
                        ExcelClone.Item("FileName") = Col.ColumnName
                        If dtFunds.Columns(Col.ColumnName).ColumnName <> MappedCol.Rows(0).Item("FieldName") Then
                            If dtFunds.Columns.Contains(MappedCol.Rows(0).Item("FieldName")) Then
                                dtFunds.Columns(Col.ColumnName).ColumnName = MappedCol.Rows(0).Item("FieldName") & "~"
                                ColumnName.Add(MappedCol.Rows(0).Item("FieldName"))
                            Else
                                dtFunds.Columns(Col.ColumnName).ColumnName = MappedCol.Rows(0).Item("FieldName")
                            End If
                        Else
                            dtFunds.Columns(Col.ColumnName).ColumnName = MappedCol.Rows(0).Item("FieldName")
                        End If
                        dtExcelClone.Rows.Add(ExcelClone)
                        ExcelClone = dtExcelClone.NewRow
                    Else
                        If Col.ColumnName <> "FileName" Then
                            dtExcelClone.Columns.Remove(Col.ColumnName)
                        End If
                        dtFunds.Columns.Remove(Col.ColumnName)
                    End If
                Next

                For Each lstrColumnName As String In ColumnName
                    dtFunds.Columns(lstrColumnName & "~").ColumnName = lstrColumnName
                Next
                'dtExcel = dtInvestor.Copy()
            Catch ex As Exception
                Throw ex
            Finally
                ReturnVal = Nothing
                MappedCol = Nothing
                ExcelClone = Nothing
                ColumnName = Nothing
                Excel = Nothing
                idr = Nothing
            End Try
            Return True

        End Function

        ''' <summary>
        ''' To select the Source Names.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function SelectSourceNames() As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(0) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@SourceTypeCode", SourceTypeCode)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_GetImportSources", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)
        End Function

        Public Function SelectImportTemplates() As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(0) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@ESourceID", ESourceID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_GetImportTemplates", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)
        End Function

#End Region

#Region "Transaction"

        Public Function getcount() As Integer
            Dim Cmd As SqlCommand = Nothing
            Cmd = New SqlCommand
            Cmd.CommandText = "cp_sp_Sel_GetTotalFunds"
            Cmd.CommandTimeout = 0
            Cmd.CommandType = CommandType.StoredProcedure
            Return CreateDataBase().ExecuteScalar(Cmd)
        End Function

        ''' <summary>
        ''' Insert Investor data into database
        ''' </summary>
        ''' <param name="strinvestorXML">Gets the Xml Structures</param>
        ''' <remarks></remarks>

        Private Function BulkInsertFund(ByVal strInvestorXML As String) As DataTable
            Dim Param(3) As SqlParameter
            Dim Cmd As SqlCommand = Nothing
            Dim DsInvestor As DataSet = Nothing
            Dim errorNo As String = ""
            Dim errorMsg As String = ""
            If strInvestorXML Is Nothing Then
                Dim lstrmsg As String = ""

            End If
            Dim specialcharacters As String = FundformatText()


            Cmd = New SqlCommand
            Cmd.CommandText = "CP_sp_FND_IMP_FundDetails"
            Cmd.CommandTimeout = 0
            Cmd.CommandType = CommandType.StoredProcedure
            Param(0) = New SqlParameter("@doc", strInvestorXML)
            Cmd.Parameters.Add(Param(0))
            'Param(1) = New SqlParameter("@Date", Convert.ToDateTime(ImportedDate).ToString("MM/dd/yyyy").Replace("-", "/"))
            'Cmd.Parameters.Add(Param(1))
            Param(2) = New SqlParameter("@UserID", UserId)
            Cmd.Parameters.Add(Param(2))
            Param(3) = New SqlParameter("@specialCharacters", specialcharacters)
            Cmd.Parameters.Add(Param(3))

            DsInvestor = CreateDataBase().ExecuteDataSet(Cmd)
            If (DsInvestor.Tables.Count = 0) Then
                Throw New Exception("Error")
            End If
            If (DsInvestor.Tables.Count = 2) Then
                If (DsInvestor.Tables(1).Columns.Contains("ErrorMessage")) Then
                    errorNo = DsInvestor.Tables(1).Rows(0).Item("ErrorNo").ToString
                    errorMsg = DsInvestor.Tables(1).Rows(0).Item("ErrorMessage").ToString

                    If errorMsg <> "" Then
                        If (errorNo = "1205") Then
                            Throw New Exception("Database cannot support more connections at this time. Please try again later. 1205")
                        Else
                            Throw New Exception(errorMsg + " " + errorNo)
                        End If

                    End If
                End If

                If (DsInvestor.Tables(0).Columns.Contains("ErrorMessage")) Then
                    errorNo = DsInvestor.Tables(0).Rows(0).Item("ErrorNo").ToString
                    errorMsg = DsInvestor.Tables(0).Rows(0).Item("ErrorMessage").ToString

                    If errorMsg <> "" Then
                        If (errorNo = "1205") Then
                            Throw New Exception("Database cannot support more connections at this time. Please try again later. 1205")
                        Else
                            Throw New Exception(errorMsg + " " + errorNo)
                        End If

                    End If
                End If

            End If
            If (DsInvestor.Tables.Count = 1) Then
                If (DsInvestor.Tables(0).Columns.Contains("ErrorMessage")) Then
                    errorNo = DsInvestor.Tables(0).Rows(0).Item("ErrorNo").ToString
                    errorMsg = DsInvestor.Tables(0).Rows(0).Item("ErrorMessage").ToString

                    If errorMsg <> "" Then
                        If (errorNo = "1205") Then
                            Throw New Exception("Database cannot support more connections at this time. Please try again later. 1205")
                        Else
                            Throw New Exception(errorMsg + " " + errorNo)
                        End If
                    End If
                End If
            End If

            Inserted = DsInvestor.Tables(1).Rows(0).Item("TotalInserted")
            Updated = DsInvestor.Tables(1).Rows(0).Item("TotalUpdate")
            Cmd.Dispose()
            Return DsInvestor.Tables(0)
            'Catch ex As Exception
            '    'GoTo a
            '    Throw ex
            'End Try
        End Function

        ''' <summary>
        ''' To get Fund Database Fields.
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>

        Public Function SelectDatabaseFields() As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            'Const ARRAY_SIZE As Integer = 0
            'Try
            Dim param(0) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@DBFieldID", DbFieldID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_sp_FND_SEL_FundDatabaseFields", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function

        ''' <summary>
        ''' Gets particular format from database
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function GetMappedColumns() As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            'Const ARRAY_SIZE As Integer = 0
            Dim param(0) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@FormatID", TemplateID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_Sp_FND_SEL_Format", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)

        End Function

        ''' <summary>
        ''' Check Format Exists In databse
        ''' </summary>
        ''' <param name="formatID"></param>
        ''' <param name="pstrExcelColName"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Private Function MapDatabaseColumn(ByVal formatID As Integer, ByVal pstrExcelColName As String) As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            'Const ARRAY_SIZE As Integer = 1
            'Try
            Dim param(1) As SqlClient.SqlParameter
            'While (True)
            '    Try
            param(0) = New SqlParameter("@FormatID", formatID)
            param(1) = New SqlParameter("@FileColumn", pstrExcelColName)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_sp_FND_EXT_FundDatabaseColumn", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)

        End Function

        ''' <summary>
        ''' Checks the mandatory field
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function ChkImportFundIsIsmandatory() As ExecutionResult
            Dim eReturnVal As ExecutionResult = Nothing
            Const ARRAY_SIZE As Integer = 2
            'Try
            Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@ETemplateID", TemplateID)
            param(1) = New SqlParameter("@SourceTypeCode", SourceTypeCode)
            param(2) = New SqlParameter("@Name", SqlDbType.VarChar, 100)
            param(2).Direction = ParameterDirection.Output
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_CheckTemplateMandatoryFields", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function

        ''' <summary>
        ''' Gets the funds format
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function SelectSourceFormat() As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            'Const ARRAY_SIZE As Integer = 0
            'Try
            Dim param(0) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@FormatID", TemplateID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_sp_FND_SEL_FundImportFormat", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function

        ''' <summary>
        ''' Gets the Fund investor parent tables
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function GetInvestorFieldTables() As IDataReader
            'constants
            'stored procedures
            'Const SEL_TestProcedure = "Cp_sp_FND_SEL_InvestorFieldsData"
            Dim dtReturn As IDataReader = Nothing
            dtReturn = CreateDataBase().ExecuteReader("Cp_sp_FND_SEL_InvestorFieldsData")

            Return dtReturn

        End Function

        ''' <summary>
        ''' Gets Country
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function GetFundFieldTables() As IDataReader
            Dim obj As ExecutionResult = Nothing
            Dim specialcharacters As String = FundformatText()
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@specialCharacters", specialcharacters)
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_FND_GetFundFieldsDetail", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(obj.ResultObject, IDataReader)
        End Function

        Public Function GetFundValidatedData() As DataSet
            Dim obj As ExecutionResult = Nothing
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_FND_ImportFundValidations", CommandType.StoredProcedure, ReturnTypes.DataSet)
            Return CType(obj.ResultObject, DataSet)
        End Function

        Public Function GetFundEntityIDByFundName(ByVal FundName As String) As String
            Dim dtReturn As String = String.Empty
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@FundName", FundName)
            dtReturn = CreateDataBase().ExecuteScalar("CR_sp_FND_GetEntityID")
            Return dtReturn

        End Function

        ''' <summary>
        ''' Gets the details of Templates Fields
        ''' </summary>
        ''' <remarks></remarks>
        ''' 

        Public Sub ClearBulkFundImport(ByVal strTableName As String)

            'Dim Param(1) As SqlParameter
            Dim Cmd As SqlCommand = Nothing
            Dim Param(1) As SqlParameter
            'Dim dtTemplateFields As DataTable = Nothing
            Dim ErrorMsg As String = ""
            Dim ErrorNo As String = ""

            Cmd = New SqlCommand
            Cmd.CommandText = "CR_sp_ClearTables"
            Cmd.CommandTimeout = 0
            Param(0) = New SqlParameter("@TableName", strTableName)
            Param(1) = New SqlParameter("@EntityID", 0)

            Cmd.CommandType = CommandType.StoredProcedure
            Cmd.Parameters.Add(Param(0))
            Cmd.Parameters.Add(Param(1))

            CreateDataBase().ExecuteNonQuery(Cmd)
            Cmd.Dispose()

        End Sub

        ''' <summary>
        ''' calls the functions for importing datatable to database
        ''' </summary>
        ''' <param name="dtInAndOutTable"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function ImportIFunds(ByRef dtInAndOutTable As DataTable) As Boolean
            'dtInAndOutTable = CreateDataTableStructure(dtInAndOutTable)
            Dim Param(0) As SqlParameter
            Dim Cmd As SqlCommand = Nothing
            Dim DsInvestor As DataSet = Nothing
            Dim ErrorMsg As String = ""
            Dim ErrorNo As String = ""

            Cmd = New SqlCommand
            Cmd.CommandText = "CR_sp_FND_ImportFunds"
            Cmd.CommandTimeout = 0
            Cmd.CommandType = CommandType.StoredProcedure
            Param(0) = New SqlParameter("@UserId", BLLmdlCommon.UserId)
            Cmd.Parameters.Add(Param(0))

            DsInvestor = CreateDataBase().ExecuteDataSet(Cmd)

            If (DsInvestor.Tables(DsInvestor.Tables.Count - 1).Columns.Contains("ErrorNumber")) Then

                ErrorMsg = DsInvestor.Tables(DsInvestor.Tables.Count - 1).Rows(0).Item("ErrorMessage")
                ErrorNo = DsInvestor.Tables(DsInvestor.Tables.Count - 1).Rows(0).Item("ErrorNumber")

                If ErrorMsg <> "" Then
                    If (ErrorNo = "1205") Then
                        Throw New Exception("Database cannot support more connections at this time. Please try again later. 1205")
                    Else
                        Throw New Exception(ErrorMsg + " " + ErrorNo)
                    End If
                End If

            End If

            Inserted = 0
            Updated = 0
            TotalNameChange = 0
            Totalacquired = 0
            TotalMerged = 0

            Cmd.Dispose()
            dtInAndOutTable = DsInvestor.Tables(DsInvestor.Tables.Count - 2)

            Return True
        End Function
        Public Function GetLatestAddedTemplate(ByVal ETemplateID As String) As DataSet
            Const ARRAY_SIZE As Integer = 0
            Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
            Dim eReturnVal As ExecutionResult = Nothing
            Dim dsReturn As DataSet = Nothing
            param(0) = New SqlParameter("@ETemplateID", ETemplateID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_GetAddedTemplate", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
            dsReturn = CType(eReturnVal.ResultObject, DataSet)
            Return dsReturn
        End Function

        Public Function GetValidatedData() As DataSet
            Dim eReturnVal As ExecutionResult = Nothing
            Dim dsReturn As DataSet = Nothing
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_FND_GetValidatedData", CommandType.StoredProcedure, ReturnTypes.DataSet)
            dsReturn = CType(eReturnVal.ResultObject, DataSet)
            Return dsReturn
        End Function
#End Region

#Region "Old"
        'Method To Get Funds
        Public Shared Function GetFunds() As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            ' Try
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_spFundGetAll", CommandType.StoredProcedure, ReturnTypes.DataReader)
            Return CType(eReturnVal.ResultObject, IDataReader)
            'Catch ex As Exception
            '    Throw ex
            'End Try

        End Function

#End Region
    End Class

End Namespace

